package org.csu.jpetstore_users_backend_pj.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import org.apache.ibatis.annotations.Update;
import org.csu.jpetstore_users_backend_pj.entity.Account;
import org.csu.jpetstore_users_backend_pj.vo.AccountVO;
import org.springframework.stereotype.Repository;

@Repository
public interface AccountMapper extends BaseMapper<Account> {
//    @Select("SELECT SIGNON.USERNAME, " +
//            "ACCOUNT.EMAIL, ACCOUNT.FIRSTNAME, ACCOUNT.LASTNAME, ACCOUNT.STATUS, " +
//            "ACCOUNT.ADDR1 AS address1, ACCOUNT.ADDR2 AS address2, " +
//            "ACCOUNT.CITY, ACCOUNT.STATE, ACCOUNT.ZIP, ACCOUNT.COUNTRY, ACCOUNT.PHONE, " +
//            "PROFILE.LANGPREF AS languagePreference, PROFILE.FAVCATEGORY AS favouriteCategoryId, " +
//            "PROFILE.MYLISTOPT AS listOption, PROFILE.BANNEROPT AS bannerOption, " +
//            "BANNERDATA.BANNERNAME " +
//            "FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA " +
//            "WHERE ACCOUNT.USERID = #{username} AND SIGNON.PASSWORD = #{password} " +
//            "AND SIGNON.USERNAME = ACCOUNT.USERID " +
//            "AND PROFILE.USERID = ACCOUNT.USERID " +
//            "AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY")
//    Account getAccountByUsernameAndPassword(
//            @Param("username") String username,
//            @Param("password") String password
//    );
@Select("SELECT PWDHASH FROM signon WHERE USERNAME = #{username}")
String getPwdHashByUsername(@Param("username") String username);

//    @Select("SELECT a.USERID AS username, a.EMAIL, a.FIRSTNAME, a.LASTNAME, a.STATUS, " +
//            "a.ADDR1 AS address1, a.ADDR2 AS address2, a.CITY, a.STATE, a.ZIP, a.COUNTRY, a.PHONE, " +
//            "p.LANGPREF AS languagePreference, p.FAVCATEGORY AS favouriteCategoryId, " +
//            "p.MYLISTOPT AS listOption, p.BANNEROPT AS bannerOption, b.BANNERNAME " +
//            "FROM ACCOUNT a " +
//            "LEFT JOIN PROFILE p ON a.USERID = p.USERID " +
//            "LEFT JOIN BANNERDATA b ON p.FAVCATEGORY = b.FAVCATEGORY " +
//            "WHERE a.PHONE = #{phone}")
//    Account getAccountByPhone(@Param("phone") String phone);
    @Select("SELECT a.userid AS username, a.email, a.firstname, a.lastname, a.status, " +
            "a.address1 AS address1, a.address2 AS address2, a.city, a.state, a.zip, a.country, a.phone, " +
            "a.gitee_id AS giteeId, a.gitee_avatar AS giteeAvatar, a.gitee_nickname AS giteeNickname, a.gitee_token AS giteeToken, " +
            "p.langpref AS languagePreference, p.favcategory AS favouriteCategoryId, " +
            "p.mylistopt AS listOption, p.banneropt AS bannerOption, b.bannername AS bannerName " +
            "FROM account a " +
            "LEFT JOIN profile p ON a.userid = p.userid " +
            "LEFT JOIN bannerdata b ON p.favcategory = b.favcategory " +
            "WHERE a.phone = #{phone}")
    Account getAccountByPhone(@Param("phone") String phone);

//    @Select("SELECT * FROM ACCOUNT WHERE USERID = #{username}")
//    Account getAccountByUsername(@Param("username") String username);
    @Select("SELECT * FROM account WHERE userid = #{username}")
    Account getAccountByUsername(@Param("username") String username);
    @Select("SELECT * FROM account WHERE EMAIL = #{email}")
    Account getAccountByEmail(@Param("email") String email);
    @Select("SELECT giteeToken FROM account WHERE giteeId = #{giteeId}")
    String getTokenByGiteeId(@Param("giteeId") String giteeId);

    @Select("SELECT COUNT(*) FROM account WHERE giteeId = #{giteeId}")
    int countByGiteeId(String giteeId);

    @Update("UPDATE account SET giteeToken = #{giteeToken} WHERE giteeId = #{giteeId}")
    void updateGiteeTokenByGiteeId(@Param("giteeId") String giteeId,
                                   @Param("giteeToken") String giteeToken);


    @Insert("INSERT INTO signon (USERNAME, PWDHASH) VALUES (#{username}, #{pwdHash})")
    void insertSignon(String username, String pwdHash);

//    @Insert("INSERT INTO ACCOUNT (USERID, EMAIL, FIRSTNAME, LASTNAME, STATUS, ADDR1, ADDR2, CITY, STATE, ZIP, COUNTRY, PHONE,GITEEID,GITEEAVATAR,GITEENICKNAME) " +
//            "VALUES (#{username}, #{email}, #{firstname}, #{lastname}, #{status}, #{address1}, #{address2}, #{city}, #{state}, #{zip}, #{country}, #{phone},#{giteeId},#{giteeAvatar},#{giteeNickname})")
//    void insertAccount(AccountVO account);
    @Insert("INSERT INTO account (userid, email, firstname, lastname, status, address1, address2, city, state, zip, country, phone, giteeId, giteeAvatar, giteeNickname, giteeToken) " +
            "VALUES (#{username}, #{email}, #{firstname}, #{lastname}, #{status}, #{address1}, #{address2}, #{city}, #{state}, #{zip}, #{country}, #{phone}, #{giteeId}, #{giteeAvatar}, #{giteeNickname}, #{giteeToken})")
    void insertAccount(AccountVO account);

//    @Insert("INSERT INTO PROFILE (USERID, LANGPREF, FAVCATEGORY, MYLISTOPT, BANNEROPT) " +
//            "VALUES (#{username}, #{languagePreference}, #{favouriteCategoryId}, #{listOption}, #{bannerOption})")
//    void insertProfile(AccountVO account);
    @Insert("INSERT INTO profile (userid, langpref, favcategory, mylistopt, banneropt) " +
            "VALUES (#{username}, #{languagePreference}, #{favouriteCategoryId}, #{listOption}, #{bannerOption})")
    void insertProfile(AccountVO account);
    @Insert("INSERT INTO ACCOUNT (giteeId,giteeAvatar,giteeNickname,giteeToken)"+
            "VALUES (#{giteeId},#{giteeAvatar},#{giteeNickname},#{giteeToken})")
    void insertGitee(AccountVO account);

    @Update("UPDATE signon SET PWDHASH = #{password} WHERE USERNAME = #{username}")
    void updatePassword(@Param("username") String username, @Param("password") String password);
}
